package com.ivo.quartz;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ivo.biz.ActualCapacityOfMon;
import com.ivo.biz.StandardCapacityOfMon;
import com.ivo.core.hibernate.HibernateInvoker;
import com.ivo.sys.Code;
import com.ivo.util.DateUtil;
import com.ivo.util.StringUtil;


@Repository
public class CapacityOfMonQuartz {
	
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	private HibernateInvoker hibernateInvoker;
	
	@Resource(name = "dsEdw")
	private DataSource dsEdwDataSource;
	
	@Resource(name = "dataSource")
	private DataSource pmsDataSource;
	
	@Resource(name = "scmdataSource")
	private DataSource scmDataSource;


	@Transactional
	@Scheduled(cron = "0 0 7 16 * ? ")
	public void execute() throws SQLException {
		logger.info("同步产能标配start");
		standardCapacity();
		logger.info("同步产能标配end");
	}
	@Transactional
	@Scheduled(cron = "0 0 8 * * ? ")
	public void execute1() throws SQLException {
		logger.info("同步实际产能start");
		actualCapacity("10M");
		actualCapacity("11M");
		logger.info("同步实际产能end");
	}
	@Scheduled(cron = "0 10 8 16 * ? ")
	public void execute2() throws SQLException {
		logger.info("计算实际产能start");
		calculate();
		logger.info("计算实际产能end");
	}
	
	
	
	
	public void actualCapacity(String fab_id) {
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DATE, -1);
		String dateOfThis = DateUtil.formatDate(cal.getTime());
		StringBuffer sql = new StringBuffer();
		StringBuffer insertSql1 = new StringBuffer();
		StringBuffer insertSql2 = new StringBuffer();
		String dateOfFab = "";
		if(fab_id.equals("10M")){
			dateOfFab = "LCM1_DAY";
		}else{
			dateOfFab = "FAB_DATE";
		}
		insertSql1.append(" INSERT INTO dl_o_actualcapacityofmon (MONTH,FAB_DATE,EMP_ID,FAB_ID,OPE_ID,MODEL_ID,OUTPUT) ");
		insertSql1.append(" VALUES (?,?,?,?,?,?,?) ");
		insertSql2.append(" INSERT INTO dl_o_actualcapacityofmon (MONTH,FAB_DATE,EMP_ID,FAB_ID,OPE_ID,MODEL_ID) ");
		insertSql2.append(" VALUES (?,?,?,?,?,?) ");
		logger.info("同步LCM实际产能start");
		JdbcTemplate JdbcTemplate = new JdbcTemplate(dsEdwDataSource);
		List <String[]> list1 = new ArrayList<String[]>();
		List <String[]> list2 = new ArrayList<String[]>();
		sql.append(" SELECT AA." + dateOfFab + ",AA.FAB_ID,AA.EVT_USER,BB.PROD_MODEL_ID,AA.CR_OPE_ID,AA.OUTPUT FROM( ");
		sql.append(" SELECT EVT_USER,PROD_KEY,S." + dateOfFab + ",S.FAB_ID,CR_OPE_ID,SUM (S.PNL_CNT) AS OUTPUT FROM SOR.WPP_MMOVEMENT S WHERE ");
		sql.append(" S." + dateOfFab + " = TO_DATE ('" + dateOfThis + "', 'yyyy-mm-DD')AND S.EVT_USER <> ' 'AND S.FAB_ID = '" + fab_id + "'AND S.EVT_USER IS NOT NULL AND EVT_TYPE IN ('LOGF', 'LFRW') AND EVT_USER IN (SELECT EVT_USER ");
		sql.append(" FROM(SELECT EVT_USER,COUNT (*) FROM (SELECT DISTINCT EVT_USER,PROD_KEY," + dateOfFab + " FROM SOR.WPP_MMOVEMENT S WHERE S." + dateOfFab + " = TO_DATE ('" + dateOfThis + "', 'yyyy-mm-DD') ");
		sql.append(" AND S.EVT_USER <> ' 'AND S.FAB_ID = '" + fab_id + "'AND S.EVT_USER IS NOT NULL AND EVT_TYPE IN ('LOGF', 'LFRW')ORDER BY EVT_USER,PROD_KEY," + dateOfFab + ") A ");
		sql.append(" GROUP BY EVT_USER HAVING COUNT (*) = 1) b)GROUP BY EVT_USER,PROD_KEY,S." + dateOfFab + ",S.FAB_ID,CR_OPE_ID ");
		sql.append(" ) AA LEFT JOIN DM.M_PROD_D BB ON AA.PROD_KEY = BB.PROD_KEY WHERE AA.OUTPUT > 100 ORDER BY EVT_USER ");
	    JdbcTemplate.query(sql.toString(), new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String month = DateUtil.formatDate(new Date()).substring(0, 7);
				String emp_id = rs.getString("EVT_USER");
				String fab_id = rs.getString("FAB_ID");
				String ope_id = rs.getString("CR_OPE_ID");
				String model_id = rs.getString("PROD_MODEL_ID");
				String output = rs.getString("OUTPUT");
				String fabDate = rs.getString(1);
				String ope_fk = "";
				Code code= hibernateInvoker.findUnique(Code.class, "from Code c where c.name = '" + ope_id + "'and c.category = '26'");
				if(code!=null ){
					Long id =code.getId(); 
					ope_fk = id + "";
				}
				if(StringUtil.isNotEmpty(emp_id)){
					if(ope_id.equals("1500") || ope_id.equals("1820") || ope_id.equals("1320") || ope_id.equals("1820")){
						String[] str = {month,fabDate,emp_id,fab_id,ope_fk,model_id,output};
						list1.add(str);
					}else{
						String[] str = {month,fabDate,emp_id,fab_id,ope_fk,model_id};
						list2.add(str);
					}
					
				}
				
			}
		});
	    batchSend(insertSql1.toString(), list1, 200);
	   // batchSend(insertSql2.toString(), list2, 200);
	    logger.info("同步LCM实际产能end");
	}
	
	public void standardCapacity(){
		Date dateOfThis = new Date();
		JdbcTemplate JdbcTemplate = new JdbcTemplate(scmDataSource);
		StringBuilder selectSql = new StringBuilder();
		selectSql.append(" select fab_id,ope,model_id,cnlow FROM scm_aps_lcm_rlbp where  FLAG = 'INLINE' AND cnlow IS NOT NULL AND MODEL_ID IS NOT NULL AND MODEL_ID <> '' AND OPE IN ('1820', 'B/L') ORDER BY FAB_ID,OPE");
		String insertSql = " INSERT INTO dl_o_standardcapacityofmon (FAB_ID,OPE_ID,MODEL_ID,CNLOW,MONTH) VALUES (?,?,?,?,?)";
		List<String[]> list = new ArrayList<String[]>();
		JdbcTemplate.query(selectSql.toString(), new RowCallbackHandler() {
		
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String fab_id = rs.getString("fab_id");
				String ope_id =rs.getString("ope");
				String model_id = rs.getString("model_id");
				String cnlow = rs.getString("cnlow");
				String ope_fk = "";
				String month = DateUtil.formatDate(dateOfThis).substring(0, 7);
				Code code= hibernateInvoker.findUnique(Code.class, "from Code c where c.name = '" + ope_id + "'and c.category = '27'");
				if(code!=null ){
					Long id =code.getId(); 
					ope_fk = id + "";
				}
				String[] str = {fab_id,ope_fk,model_id,cnlow,month};
				list.add(str);
		}
		});
		batchSend(insertSql, list, 200);
	}
	
	public void batchSend(String sql, List<String[]> list, int batchSize){
		int count = 0;
		Connection conn = null;
		PreparedStatement statement = null;
		long startTime = System.currentTimeMillis();
		try{
			conn = pmsDataSource.getConnection();
			conn.setAutoCommit(false);
			statement = conn.prepareStatement(sql);
			if(list.size() > 0){
				for(String [] li : list){
					int columnIndex = 0;  
					for(String o : li){
						statement.setString(++columnIndex, o);
					}
					statement.addBatch();
					count++;
					if(count % batchSize == 0){
						statement.executeBatch();
						conn.commit();
						statement.clearBatch();
						logger.info("done " + count + "笔数据, 耗时: " + (System.currentTimeMillis() - startTime));
					}
				}
				
					statement.executeBatch();
					conn.commit();
					logger.info("done " + count + "笔数据, 耗时: " + (System.currentTimeMillis() - startTime));
				
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				if(conn != null)
					conn.close();
				if(statement != null)
					statement.close();
			} catch (SQLException e) {
				e.printStackTrace();

			}
		
		}
		
	}
	
	@SuppressWarnings("rawtypes")
	public void calculate(){
		long a=System.currentTimeMillis();
		String month = DateUtil.formatDate(new Date()).substring(0, 7);
		List<String[]> list = new ArrayList<String[]>();
		List<String> emp_idList = new ArrayList<String>();
		Map<String, String> standardCapacity = new LinkedHashMap<String, String>();
		Map<String, String> actualCapacity = new LinkedHashMap<String, String>();
		List<ActualCapacityOfMon> actualList = hibernateInvoker.find(ActualCapacityOfMon.class, "from ActualCapacityOfMon ac where ac.month = '" + month + "' group by ac.emp_id");
		for(ActualCapacityOfMon actual : actualList){
			emp_idList.add(actual.getEmp_id());
		}
		//int x = 1;
		for(String empid : emp_idList){
			double output = 0.0;
			double cnlow = 0;
			List<ActualCapacityOfMon> personActualList = hibernateInvoker.find(ActualCapacityOfMon.class, "from ActualCapacityOfMon ac where ac.emp_id = '" + empid + "' and ac.month = '" + month + "'");
			for(ActualCapacityOfMon ac : personActualList){
				String ope_id = ac.getOpe_id();
				if(ac.getOpe_id().equals("232")){
					ope_id = "294";
				}else if(ac.getOpe_id().equals("227") || ac.getOpe_id().equals("239")){
					ope_id = "293";
				}
				if(StringUtil.isNotEmpty(ac.getOutput())){
						output = output +  Double.parseDouble(ac.getOutput());
						List<StandardCapacityOfMon> personStandardList = hibernateInvoker.find(StandardCapacityOfMon.class, "from StandardCapacityOfMon sc where sc.fab_id = '" + (ac.getFab_id().equals("10M")?"M1":"M2") + "' and sc.month = '" + month + "' and sc.ope_id = '" + ope_id + "' and sc.model_id like '%" + ac.getModel_id().substring(1, 5) + "'");
						if(personStandardList.size() == 1){
							cnlow = cnlow + Double.parseDouble(personStandardList.get(0).getCnlow());
					}
				}
			}
			actualCapacity.put(empid , output + "");
			standardCapacity.put(empid , cnlow + "");
			//x++;
			//ySystem.out.println(x);
		}
		for(Map.Entry<String, String> actual : actualCapacity.entrySet()){
			for(Map.Entry<String, String> standard : standardCapacity.entrySet()){
				if(actual.getKey().equals(standard.getKey())){
					String [] str = {actual.getKey() , actual.getValue() , standard.getValue()};
					list.add(str);
				}
				
			}
		}
		logger.info("耗时: " + (System.currentTimeMillis() - a));
				String sql = "UPDATE dl_m_performancemon SET OUTPUT = ?,CNLOW = ? WHERE EMP_ID = ?  AND MONTH = '" + month + "'";
				JdbcTemplate  jdbctemp = new JdbcTemplate(pmsDataSource);
				long startTime = System.currentTimeMillis();
				jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() { 
					public int getBatchSize() {  
		                return list.size();  
		            }  
					public void setValues(PreparedStatement ps, int i)throws SQLException {
							ps.setString(1, list.get(i)[1]);
							ps.setString(2, list.get(i)[2]);
							ps.setString(3, list.get(i)[0]);
						
							
		            }  
				});
				logger.info("耗时: " + (System.currentTimeMillis() - startTime));
				
	}




}
